package ro.gateway.aida.obj.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ro.gateway.aida.db.DBPersistenceManager;
import ro.gateway.aida.db.PersistenceToken;
import ro.gateway.aida.obj.AIDAActivityObject;
import ro.gateway.aida.obj.OrganizationInv;
import ro.gateway.aida.obj.OrganizationRole;
import ro.gateway.aida.srv.IIDGenerator;



/**
 * <p>Title: Romanian AIDA</p>
 * <p>Description: :D application</p>
 * <p>Copyright: Copyright (comparator) 2003</p>
 * <p>Company: Romania Development Gateway </p>
 * @author Mihai Popoaei, mihai_popoaei@yahoo.com, smike@intellisource.ro
 * @version 1.0-* @version $Id: OrganizationInvDB.java,v 1.1 2004/10/24 23:37:11 mihaipostelnicu Exp $
 */
public class OrganizationInvDB extends DBPersistenceManager
{
      private OrganizationInvDB( PersistenceToken token )
      {
            super( token );
      }

      public static OrganizationInvDB getManager( PersistenceToken token )
      {
            return new OrganizationInvDB( token );
      }


      public OrganizationInv[] getForActivity( long activityId ) throws SQLException
      {
            OrganizationInv[] result = null;

            Connection con = getConnection();
            ArrayList items = new ArrayList();

            try
            {
                  PreparedStatement ps = con.prepareStatement( GET_IDS_AND_ROLES );
                  ps.setLong( 1, activityId );

                  ResultSet rs = ps.executeQuery();

                  while ( rs.next() )
                  {
                        OrganizationInv item = new OrganizationInv();
                        item.setOrg_id( rs.getLong( 1 ) );

                        int role_id = rs.getInt( 2 );

                        if ( rs.wasNull() )
                              role_id = -1;

                        item.setId( rs.getLong( 3 ) );

                        item.role = OrganizationRole.getCachedByLocalId( new Integer(
                              role_id ) );
                        items.add( item );
                  }

                  rs.close();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }

            if ( items.size() > 0 )
            {
                  result = new OrganizationInv[ items.size() ];
                  items.toArray( result );
            }

            if ( result != null )
                  for ( int i = 0; i < result.length; i++ )
                        result[ i ].setOrg( ( OrganizationDB.getManager( token ) ).getByID(
                              result[ i ].getOrg_id() ) );

            return result;
      }

      public void delete( long id ) throws SQLException
      {
            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE );
                  ps.setLong( 1, id );
                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }
      }

      public final void deleteForActivity( long activity_id ) throws SQLException
      {
            Connection con = getConnection();
            String database_pname = con.getMetaData().getDatabaseProductName();

            if ( 0 == database_pname.compareToIgnoreCase( "MYSQL" ) )
            {
                  System.out.println(
                        "MYSQL - deleteForActivity (organizationsinvolved) -- DEBUG" );
                  con.close();
                  deleteForActivityMYSQL( activity_id );

                  return;
            }

            try
            {
                  PreparedStatement ps = con.prepareStatement( DELETE_FOR_ACTIVITY );
                  ps.setLong( 1, activity_id );
                  ps.executeUpdate();
                  ps.close();

                  AIDAActivityObjectDB.getManager( token ).deleteForActivityByType( con, activity_id,
                        AIDAActivityObject.TYPE_ORGANIZATION_INVOLVED );
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }
      }

      private void deleteForActivityMYSQL( long activityId ) throws SQLException
      {
            OrganizationInv[] orgs = getForActivity( activityId );
            System.out.println( "org_involved:" + orgs );

            if ( orgs != null )
            {
                  Connection con = getConnection();

                  try
                  {
                        PreparedStatement ps = con.prepareStatement(
                              "DELETE FROM orgs_inv WHERE id = ?" );

                        for ( int i = 0; i < orgs.length; i++ )
                        {
                              System.out.println( "deleting:" + orgs[ i ].getId() );
                              ps.clearParameters();
                              ps.setLong( 1, orgs[ i ].getId() );
                              ps.executeUpdate();
                        }

                        ps.close();
                        AIDAActivityObjectDB.getManager( token ).deleteForActivityByType( con, activityId,
                              AIDAActivityObject.TYPE_ORGANIZATION_INVOLVED );
                  }
                  catch ( SQLException sqlEx )
                  {
                        throw sqlEx;
                  }
                  finally
                  {
                        con.close();
                  }
            }
      }

      public void insert( IIDGenerator idGenerators, OrganizationInv item )
            throws SQLException
      {
            AIDAActivityObjectDB.getManager( token ).insert( idGenerators, item );

            Connection con = getConnection();

            try
            {
                  PreparedStatement ps = con.prepareStatement( INSERT );

                  ps.setLong( 1, item.getId() );
                  ps.setLong( 2, item.getOrg_id() );
                  ps.setInt( 3, item.role.getId().intValue() );

                  ps.executeUpdate();
                  ps.close();
            }
            catch ( SQLException sqlEx )
            {
                  throw sqlEx;
            }
            finally
            {
                  con.close();
            }
      }

      private static final String GET_IDS_AND_ROLES =
            "SELECT oi.org_id, oi.role, oi.id " + "FROM orgs_inv oi, objects o " +
            "WHERE o.id=oi.id AND activity_id = ?";
      private static final String DELETE = "DELETE FROM orgs_inv WHERE id = ?";
      private static final String DELETE_FOR_ACTIVITY = "DELETE FROM orgs_inv WHERE id IN (SELECT id FROM objects WHERE activity_id=?)";
      private static final String INSERT =
            "INSERT INTO orgs_inv (id, org_id, role) " + "VALUES (?,?,?)";
}
